<?php

namespace common\widgets\phpcsv;

use Yii;
use yii\helpers\ArrayHelper;
use yii\base\InvalidConfigException;
use yii\base\InvalidParamException;
use yii\i18n\Formatter;
use yii\base\InvalidArgumentException;

/**
 * Excel Widget for generate Excel File or for load Excel File.
 *
 * Usage
 * -----
 *
 * Exporting data into an excel file.
 *
 * ~~~
 *
 * Result example from the code on the top :
 *
 * ~~~
 *
 * // only one sheet or specified sheet.
 *
 * Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2));
 *
 * // data with multiple worksheet
 *
 * Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)),
 * [Sheet2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)));
 *
 * // data with multiple file and specified sheet or only one worksheet
 *
 * Array([file1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)),
 * [file2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)));
 *
 * // data with multiple file and multiple worksheet
 *
 * Array([file1] => Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)),
 * [Sheet2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2))),
 * [file2] => Array([Sheet1] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2)),
 * [Sheet2] => Array([0] => Array([name] => Anam, [email] => moh.khoirul.anaam@gmail.com, [framework interest] => Yii2),
 * [1] => Array([name] => Example, [email] => example@moonlandsoft.com, [framework interest] => Yii2))));
 *
 * ~~~
 *
 * @property string $mode is an export mode or import mode. valid value are 'export' and 'import'
 * @property array $properties for set property on the excel object.
 * @property array $models Model object or DataProvider object with much data.
 * @property array $columns to get the attributes from the model, this valid value only the exist attribute on the model.
 * If this is not set, then all attribute of the model will be set as columns.
 * @property array $headers to set the header column on first line. Set this if want to custom header.
 * If not set, the header will get attributes label of model attributes.
 * @property string|array $fileName is a name for file name to export or import. Multiple file name only use for import mode, not work if you use the export mode.
 * @property string $savePath is a directory to save the file or you can blank this to set the file as attachment.
 * @property string $format for excel to export. Valid value are 'Xls','Xlsx','Xml','Ods','Slk','Gnumeric','Csv', and 'Html'.
 * @property boolean $setFirstTitle to set the title column on the first line. The columns will have a header on the first line.
 * @property boolean $asAttachment to set the file excel to download mode.
 * If you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
 * @property array|Formatter $formatter the formatter used to format model attribute values into displayable texts.
 * This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
 * instance. If this property is not set, the "formatter" application component will be used.
 *
 * @author Moh Khoirul Anam <moh.khoirul.anaam@gmail.com>
 * @copyright 2014
 * @since 1
 */
class CsvExport extends \yii\base\Widget {

    // Border style
    const BORDER_NONE = 'none';
    const BORDER_DASHDOT = 'dashDot';
    const BORDER_DASHDOTDOT = 'dashDotDot';
    const BORDER_DASHED = 'dashed';
    const BORDER_DOTTED = 'dotted';
    const BORDER_DOUBLE = 'double';
    const BORDER_HAIR = 'hair';
    const BORDER_MEDIUM = 'medium';
    const BORDER_MEDIUMDASHDOT = 'mediumDashDot';
    const BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot';
    const BORDER_MEDIUMDASHED = 'mediumDashed';
    const BORDER_SLANTDASHDOT = 'slantDashDot';
    const BORDER_THICK = 'thick';
    const BORDER_THIN = 'thin';
    // Colors
    const COLOR_BLACK = 'FF000000';
    const COLOR_WHITE = 'FFFFFFFF';
    const COLOR_RED = 'FFFF0000';
    const COLOR_DARKRED = 'FF800000';
    const COLOR_BLUE = 'FF0000FF';
    const COLOR_DARKBLUE = 'FF000080';
    const COLOR_GREEN = 'FF00FF00';
    const COLOR_DARKGREEN = 'FF008000';
    const COLOR_YELLOW = 'FFFFFF00';
    const COLOR_DARKYELLOW = 'FF808000';
    // Horizontal alignment styles
    const HORIZONTAL_GENERAL = 'general';
    const HORIZONTAL_LEFT = 'left';
    const HORIZONTAL_RIGHT = 'right';
    const HORIZONTAL_CENTER = 'center';
    const HORIZONTAL_CENTER_CONTINUOUS = 'centerContinuous';
    const HORIZONTAL_JUSTIFY = 'justify';
    const HORIZONTAL_FILL = 'fill';
    const HORIZONTAL_DISTRIBUTED = 'distributed'; // Excel2007 only
    // Vertical alignment styles
    const VERTICAL_BOTTOM = 'bottom';
    const VERTICAL_TOP = 'top';
    const VERTICAL_CENTER = 'center';
    const VERTICAL_JUSTIFY = 'justify';
    const VERTICAL_DISTRIBUTED = 'distributed'; // Excel2007 only
    // Read order
    const READORDER_CONTEXT = 0;
    const READORDER_LTR = 1;
    const READORDER_RTL = 2;
    // Fill types
    const FILL_NONE = 'none';
    const FILL_SOLID = 'solid';
    const FILL_GRADIENT_LINEAR = 'linear';
    const FILL_GRADIENT_PATH = 'path';
    const FILL_PATTERN_DARKDOWN = 'darkDown';
    const FILL_PATTERN_DARKGRAY = 'darkGray';
    const FILL_PATTERN_DARKGRID = 'darkGrid';
    const FILL_PATTERN_DARKHORIZONTAL = 'darkHorizontal';
    const FILL_PATTERN_DARKTRELLIS = 'darkTrellis';
    const FILL_PATTERN_DARKUP = 'darkUp';
    const FILL_PATTERN_DARKVERTICAL = 'darkVertical';
    const FILL_PATTERN_GRAY0625 = 'gray0625';
    const FILL_PATTERN_GRAY125 = 'gray125';
    const FILL_PATTERN_LIGHTDOWN = 'lightDown';
    const FILL_PATTERN_LIGHTGRAY = 'lightGray';
    const FILL_PATTERN_LIGHTGRID = 'lightGrid';
    const FILL_PATTERN_LIGHTHORIZONTAL = 'lightHorizontal';
    const FILL_PATTERN_LIGHTTRELLIS = 'lightTrellis';
    const FILL_PATTERN_LIGHTUP = 'lightUp';
    const FILL_PATTERN_LIGHTVERTICAL = 'lightVertical';
    const FILL_PATTERN_MEDIUMGRAY = 'mediumGray';
    // Pre-defined formats
    const FORMAT_GENERAL = 'General';
    const FORMAT_TEXT = '@';
    const FORMAT_NUMBER = '0';
    const FORMAT_NUMBER_00 = '0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
    const FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';
    const FORMAT_PERCENTAGE = '0%';
    const FORMAT_PERCENTAGE_00 = '0.00%';
    const FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd';
    const FORMAT_DATE_YYYYMMDD = 'yy-mm-dd';
    const FORMAT_DATE_DDMMYYYY = 'dd/mm/yy';
    const FORMAT_DATE_DMYSLASH = 'd/m/yy';
    const FORMAT_DATE_DMYMINUS = 'd-m-yy';
    const FORMAT_DATE_DMMINUS = 'd-m';
    const FORMAT_DATE_MYMINUS = 'm-yy';
    const FORMAT_DATE_XLSX14 = 'mm-dd-yy';
    const FORMAT_DATE_XLSX15 = 'd-mmm-yy';
    const FORMAT_DATE_XLSX16 = 'd-mmm';
    const FORMAT_DATE_XLSX17 = 'mmm-yy';
    const FORMAT_DATE_XLSX22 = 'm/d/yy h:mm';
    const FORMAT_DATE_DATETIME = 'd/m/yy h:mm';
    const FORMAT_DATE_TIME1 = 'h:mm AM/PM';
    const FORMAT_DATE_TIME2 = 'h:mm:ss AM/PM';
    const FORMAT_DATE_TIME3 = 'h:mm';
    const FORMAT_DATE_TIME4 = 'h:mm:ss';
    const FORMAT_DATE_TIME5 = 'mm:ss';
    const FORMAT_DATE_TIME6 = 'h:mm:ss';
    const FORMAT_DATE_TIME7 = 'i:s.S';
    const FORMAT_DATE_TIME8 = 'h:mm:ss;@';
    const FORMAT_DATE_YYYYMMDDSLASH = 'yy/mm/dd;@';
    const FORMAT_CURRENCY_USD_SIMPLE = '"$"#,##0.00_-';
    const FORMAT_CURRENCY_USD = '$#,##0_-';
    const FORMAT_CURRENCY_EUR_SIMPLE = '#,##0.00_-"€"';
    const FORMAT_CURRENCY_EUR = '#,##0_-"€"';

    /**
     * @var string mode is an export mode or import mode. valid value are 'export' and 'import'.
     */
    public $mode = 'export';

    /**
     * @var \yii\db\ActiveQuery Model object or DataProvider object with much data.
     */
    public $query;

    /**
     * @var array Model object or DataProvider object with much data.
     */
    public $models;

    /**
     * @var array columns to get the attributes from the model, this valid value only the exist attribute on the model.
     * If this is not set, then all attribute of the model will be set as columns.
     */
    public $columns = [];

    /**
     * @var array header to set the header column on first line. Set this if want to custom header.
     * If not set, the header will get attributes label of model attributes.
     */
    public $headers = [];

    /**
     * @var string|array name for file name to export or save.
     */
    public $fileName;

    /**
     * @var string save path is a directory to save the file or you can blank this to set the file as attachment.
     */
    public $savePath;

    /**
     * @var string format for excel to export. Valid value are 'Xls','Xlsx','Xml','Ods','Slk','Gnumeric','Csv', and 'Html'.
     */
    public $format;

    /**
     * @var boolean to set the title column on the first line.
     */
    public $setFirstTitle = true;

    /**
     * @var boolean to set the file excel to download mode.
     */
    public $asAttachment = false;

    /**
     * @var boolean to set the import data will return as array.
     */
    public $asArray;

    /**
     * @var array|Formatter the formatter used to format model attribute values into displayable texts.
     * This can be either an instance of [[Formatter]] or an configuration array for creating the [[Formatter]]
     * instance. If this property is not set, the "formatter" application component will be used.
     */
    public $formatter;

    /**
     * (non-PHPdoc)
     * @see \yii\base\Object::init()
     */
    public function init() {
        parent::init();
        if ($this->formatter == null) {
            $this->formatter = Yii::$app->getFormatter();
        } elseif (is_array($this->formatter)) {
            $this->formatter = Yii::createObject($this->formatter);
        }
        if (!$this->formatter instanceof Formatter) {
            throw new InvalidConfigException('The "formatter" property must be either a Format object or a configuration array.');
        }
        $this->formatter->nullDisplay = '';
    }

    /**
     * Setting header to download generated file xls
     */
    public function setHeaders() {
        header('Content-Type: text/csv;charset=utf-8');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $this->getFileName() . '"');
        header('Cache-Control: max-age=0');
        header('Access-Control-Allow-Origin: *');
    }

    /**
     * Getting the file name of exporting xls file
     * @return string
     */
    public function getFileName() {
        $fileName = 'exports.csv';
        if (isset($this->fileName)) {
            $fileName = $this->fileName;
            if (strpos($fileName, '.csv') === false) {
                $fileName .= '.csv';
            }
        }
        return $fileName;
    }

    /**
     * (non-PHPdoc)
     * @see \yii\base\Widget::run()
     */
    public function run() {
        if ($this->asAttachment) {
            $this->setHeaders();
        }
        $this->writeFile();
    }

    /**
     * saving the xls file to download or to path
     */
    public function writeFile() {
        $path = 'php://output';
        if (isset($this->savePath) && $this->savePath != null) {
            $path = $this->savePath . '/' . $this->getFileName();
        }
        $fp = fopen($path, 'w');
        //add BOM to fix UTF-8 in Excel
        //fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
        fputs($fp, $bom = ( chr(0xEF) . chr(0xBB) . chr(0xBF) ));
        $this->executeColumns($fp, isset($this->columns) ? $this->populateColumns($this->columns) : [], isset($this->headers) ? $this->headers : []);
        if ($path == 'php://output') {
            exit();
        }
        fclose($fp);
    }

    /**
     * Populating columns for checking the column is string or array. if is string this will be checking have a formatter or header.
     * @param array $columns
     * @throws InvalidParamException
     * @return multitype:multitype:array
     */
    public function populateColumns($columns = []) {
        $_columns = [];
        foreach ($columns as $key => $value) {
            if (is_string($value)) {
                $value_log = explode(':', $value);
                $_columns[$key] = ['attribute' => $value_log[0]];
                if (isset($value_log[1]) && $value_log[1] !== null) {
                    $_columns[$key]['format'] = $value_log[1];
                }
                if (isset($value_log[2]) && $value_log[2] !== null) {
                    $_columns[$key]['header'] = $value_log[2];
                }
            } elseif (is_array($value)) {
                if (!isset($value['attribute']) && !isset($value['value'])) {
                    throw new InvalidArgumentException('Attribute or Value must be defined.');
                }
                $_columns[$key] = $value;
            }
        }
        return $_columns;
    }

    /**
     * Setting data from models
     * @param type $fp
     * @param type $columns
     * @param type $headers
     */
    public function executeColumns($fp, $columns = [], $headers = []) {
        /* @var $query \yii\db\ActiveQuery */
        $query = $this->query;
        $query->debug();
        $hasHeader = false;
        foreach ($query->batch() as $items) {
            if (!$hasHeader && $this->setFirstTitle) {
                $header = $this->renderHeaders($items[0], $columns, $headers);
                fputcsv($fp, $header);
                $hasHeader = true;
            }
            ob_flush();
            flush();
            foreach ($items as $item) {
                $row = $this->renderItem($item, $columns);
                fputcsv($fp, $row);
                unset($row);
            }
        }
    }

    public function renderItem($model, $columns) {
        $cell = [];
        foreach ($columns as $column) {
            if (is_array($column)) {
                $column_value = $this->executeGetColumnData($model, $column);
            } else {
                $column_value = $this->executeGetColumnData($model, ['attribute' => $column]);
            }
            $cell[] = $column_value;
        }
        return $cell;
    }

    /**
     * 输出表头
     * @param \yii\db\ActiveRecord $model
     * @param array $columns
     * @param array $headers
     * @return array
     */
    public function renderHeaders($model, $columns = [], $headers = []) {
        $headerColumns = [];
        foreach ($columns as $column) {
            if (is_array($column)) {
                if (isset($column['header'])) {
                    $header = $column['header'];
                } elseif (isset($column['attribute']) && isset($headers[$column['attribute']])) {
                    $header = $headers[$column['attribute']];
                } elseif (isset($column['attribute'])) {
                    $header = $model->getAttributeLabel($column['attribute']);
                }
            } else {
                $header = $model->getAttributeLabel($column);
            }
            $headerColumns[] = $header;
        }
        return $headerColumns;
    }

    /**
     * Getting column value.
     * @param \common\db\ActiveRecord $model
     * @param array $params
     * @return array
     */
    public function executeGetColumnData($model, $params = []) {
        $value = ArrayHelper::getValue($params, 'value');
        $attribute = ArrayHelper::getValue($params, 'attribute');
        $format = ArrayHelper::getValue($params, 'format');
        if ($value !== null) {
            if (is_string($value)) {
                $value = ArrayHelper::getValue($model, $value);
            } elseif (is_callable($value)) {
                $value = call_user_func($value, $model, $this);
            }
        } else {
            if ($attribute !== null) {
                if ($format == 'constLabel') {
                    $value = $model->getMarkLabel($attribute, 'language');
                    $format = 'text';
                } elseif ($format == 'booleanText') {
                    $value = boolval($value) ? '是' : '否';
                    $format = 'text';
                } else {
                    $value = ArrayHelper::getValue($model, $attribute);
                }
            }
        }
        if ($format != null) {
            $value = $this->formatter->format($value, $format);
        }
        return $value;
    }

    /**
     * Exporting data into an excel file.
     *
     * @param array $config
     * @return string
     */
    public static function export($config = []) {
        return static::widget($config);
    }

}
